/*
 * db_connection.c
 *
 *  Created on: 2013-2-14
 *      Author: panqiuhui
 */
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "../head/dao/db_connection.h"
#define GET_USERINFO_STMT "SELECT * FROM customers WHERE username=? AND password=?"
#define UPDATE_USERINFO_STMT "UPDATE customers SET name=?, gender=?, birthday=?, country=?, creditType=?, creditID=?, mobilephoneNum=?, fixedphoneNum=?, email=?, address=?, postcode=? WHERE username=?"
#define CHANGE_PASSWORD_STMT "UPDATE customers SET password=? WHERE username=? AND password=?"
#define INSERT_USERINFO_STMT "INSERT INTO customers(username,password,name,gender,birthday,country,creditType,creditID,mobilephoneNum,fixedphoneNum,email,address,postcode,type) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

#define GET_STATIONSINFO_STMT "SELECT stationName,city,startSaleTime FROM stations WHERE stationName=?"
#define GET_TRAININFO_STMT "SELECT trainID, type, departStation, arriveStation, departTime, arriveTime, consumeTime FROM trains WHERE trainID=?"
#define GET_TRAIN_THROUGH_STATIONSINFO_STMT "SELECT throughStation, stationArriveTime, stationDepartTime, consumeTime, price FROM trainthroughstations t1 LEFT JOIN ticketsinfo t2 ON (t1.trainID = t2.trainID AND t1.throughStation = t2.arriveStation) WHERE t1.trainID = ? AND (t2.departDate = ? OR t2.departDate IS NULL) AND (t2.departStation = ? OR t2.departStation IS NULL) ORDER BY t1.distance ASC"
#define GET_START_END_STATIONINFO_STMT "SELECT DISTINCT trainID,departStation,arriveStation,price FROM ticketsinfo t ,stations s1 , stations s2 WHERE t.departStation = s1.stationName AND t.arriveStation = s2.stationName AND s1.city = ? AND s2.city = ? AND t.departDate = ?"
#define GET_STATIONS_TIMEINFO_STMT "SELECT t1.stationDepartTime, t2.stationArriveTime, t1.stationArriveTime, t1.consumeTime, t2.consumeTime FROM trainthroughstations t1, trainthroughstations t2 WHERE t1.trainID = t2.trainID AND t1.trainID = ? AND t2.trainID = ? AND t1.throughStation = ? AND t2.throughStation = ?"
#define GET_THROUGHSTATION_TIMEINFO_STMT "SELECT t.trainID, t.throughStation, t.stationArriveTime, t.stationDepartTime FROM trainthroughstations t, stations s WHERE t.throughStation = s.stationName AND s.city = ?"

#define GET_ORDERINFO_BY_BUY_DATE_STMT "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND buyDate>=? AND buyDate<=?"
#define GET_ORDERINFO_BY_BUY_DATE_STMT_WITH_ORDERID "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND buyDate>=? AND buyDate<=? AND orderID=?"
#define GET_ORDERINFO_BY_BUY_DATE_STMT_WITH_TRAINID "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND buyDate>=? AND buyDate<=? AND trainID=?"
#define GET_ORDERINFO_BY_BUY_DATE_STMT_WITH_ALL "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND buyDate>=? AND buyDate<=? AND orderID=? AND trainID=?"
#define GET_ORDERINFO_BY_DEPART_DATE_STMT "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND departDate>=? AND departDate<=?"
#define GET_ORDERINFO_BY_DEPART_DATE_STMT_WITH_ORDERID "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND departDate>=? AND departDate<=? AND orderID=?"
#define GET_ORDERINFO_BY_DEPART_DATE_STMT_WITH_TRAINID "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND departDate>=? AND departDate<=? AND trainID=?"
#define GET_ORDERINFO_BY_DEPART_DATE_STMT_WITH_ALL "SELECT orderID,trainID,compartmentNum,seatNum,name,creditType,creditID,departDate,departStation,arriveStation,price FROM view_orders WHERE username=? AND departDate>=? AND departDate<=? AND orderID=? AND trainID=?"
#define DELETE_ORDERINFO_STMT "DELETE From orders WHERE orderID=? AND username=?"

#define GET_TRAININFO_FOR_TICKETLEFT_STMT "SELECT DISTINCT trainID,departStation,arriveStation,departDate,price FROM ticketsinfo t ,stations s1 , stations s2 WHERE t.departStation = s1.stationName AND t.arriveStation = s2.stationName AND s1.city = ? AND s2.city = ? AND t.departDate = ?"
#define GET_TRAININFO_FOR_TICKETLEFT_WITH_TRAINID_STMT "SELECT DISTINCT trainID,departStation,arriveStation,departDate,price FROM ticketsinfo t ,stations s1 , stations s2 WHERE t.departStation = s1.stationName AND t.arriveStation = s2.stationName AND s1.city = ? AND s2.city = ? AND t.departDate = ? AND t.trainID = ?"
#define GET_TRAIN_SEATCOUNT_STMT "SELECT compartmentNum, seatCount FROM compartments WHERE trainID = ?"
#define GET_TRAIN_SALE_TICKETCOUNT_STMT "SELECT COUNT(s.seatNum) as ticketSaledCount FROM seats s LEFT JOIN orders o ON (s.trainID = o.trainID AND s.compartmentNum = o.compartmentNum AND s.seatNum = o.seatNum) WHERE s.trainID = ? AND o.departDate = ?"
#define INSERT_ORDERINFO_STMT "INSERT INTO orders(trainID,compartmentNum,seatNum,username,departDate,departStation,arriveStation,buyDate,price) VALUES(?,?,?,?,?,?,?,?,?)"

MYSQL connection;
MYSQL_STMT *get_userinfo_stmt;
MYSQL_STMT *update_userinfo_stmt;
MYSQL_STMT *change_password_stmt;
MYSQL_STMT *register_new_user_stmt;

MYSQL_STMT *get_stationinfo_stmt;
MYSQL_STMT *get_traininfo_stmt;
MYSQL_STMT *get_train_through_stationsinfo_stmt;
MYSQL_STMT *get_start_end_stationsinfo_stmt;
MYSQL_STMT *get_stations_timeinfo_stmt;
MYSQL_STMT *get_throughstation_timeinfo_stmt;

MYSQL_STMT *get_orderinfo_by_buy_date_stmt;
MYSQL_STMT *get_orderinfo_by_buy_date_with_orderID_stmt;
MYSQL_STMT *get_orderinfo_by_buy_date_with_trainID_stmt;
MYSQL_STMT *get_orderinfo_by_buy_date_with_all_stmt;
MYSQL_STMT *get_orderinfo_by_depart_date_stmt;
MYSQL_STMT *get_orderinfo_by_depart_date_with_orderID_stmt;
MYSQL_STMT *get_orderinfo_by_depart_date_with_trainID_stmt;
MYSQL_STMT *get_orderinfo_by_depart_date_with_all_stmt;
MYSQL_STMT *delete_orderinfo_stmt;

MYSQL_STMT *get_traininfo_for_ticketleft_stmt;
MYSQL_STMT *get_traininfo_for_ticketleft_with_trainID_stmt;
MYSQL_STMT *get_train_seatcount_stmt;
MYSQL_STMT *get_train_sale_ticketcount_stmt;
MYSQL_STMT *insert_orderinfo_stmt;

//创建一个预处理语句
MYSQL_STMT *create_stmt(MYSQL_STMT *stmt, const char *query, const char *name,
		int param_count) {
	stmt = mysql_stmt_init(&connection);
	if (!stmt) {
		fprintf(stderr, "%s mysql_stmt_init(), out of memory\n", name);
		exit(EXIT_FAILURE);
	}
	if (mysql_stmt_prepare(stmt, query, strlen(query))) {
		fprintf(stderr, "%s mysql_stmt_prepare() failed\n", name);
		fprintf(stderr, "%s\n", mysql_stmt_error(stmt));
		exit(EXIT_FAILURE);
	}
	int temp_param_count = mysql_stmt_param_count(stmt);
	if (temp_param_count != param_count)  //validate parameter count
			{
		fprintf(stderr, "%s invalid parameter count returned by MySQL\n", name);
		exit(EXIT_FAILURE);
	}
	//printf("%s: %d\n", name, param_count);
	return stmt;
}

//释放一个预处理语句的资源
void close_stmt(MYSQL_STMT *stmt, const char *dao_name, const char *stmt_name) {
	if (mysql_stmt_close(stmt)) {
		fprintf(stderr, "%s failed while closing the %s statement\n", dao_name,
				stmt_name);
		fprintf(stderr, "%s\n", mysql_stmt_error(stmt));
		exit(EXIT_FAILURE);
	}
}

//创建用户相关的各个预处理语句
void create_userinfo_stmt() {
	get_userinfo_stmt = create_stmt(get_userinfo_stmt, GET_USERINFO_STMT,
			"get_userinfo_stmt", 2);
	update_userinfo_stmt = create_stmt(update_userinfo_stmt,
			UPDATE_USERINFO_STMT, "update_userinfo_stmt", 12);
	change_password_stmt = create_stmt(change_password_stmt,
			CHANGE_PASSWORD_STMT, "change_password_stmt", 3);
	register_new_user_stmt = create_stmt(register_new_user_stmt,
			INSERT_USERINFO_STMT, "register_new_user_stmt", 14);
}

//关闭用户相关的预处理语句
void close_userinfo_stmt() {
	close_stmt(get_userinfo_stmt, "userinfo_dao", "get_userinfo_stmt");
	close_stmt(update_userinfo_stmt, "userinfo_dao", "update_userinfo_stmt");
	close_stmt(change_password_stmt, "userinfo_dao", "change_password_stmt");
	close_stmt(register_new_user_stmt, "userinfo_dao",
			"register_new_user_stmt");
}

//创建火车相关的各个预处理语句
void create_traininfo_stmt() {
	get_stationinfo_stmt = create_stmt(get_stationinfo_stmt,
			GET_STATIONSINFO_STMT, "get_stationinfo_stmt", 1);
	get_traininfo_stmt = create_stmt(get_traininfo_stmt, GET_TRAININFO_STMT,
			"get_traininfo_stmt", 1);
	get_train_through_stationsinfo_stmt = create_stmt(
			get_train_through_stationsinfo_stmt,
			GET_TRAIN_THROUGH_STATIONSINFO_STMT,
			"get_train_through_stationsinfo_stmt", 3);
	get_start_end_stationsinfo_stmt = create_stmt(
			get_start_end_stationsinfo_stmt, GET_START_END_STATIONINFO_STMT,
			"get_start_end_stationsinfo_stmt", 3);
	get_stations_timeinfo_stmt = create_stmt(get_stations_timeinfo_stmt,
			GET_STATIONS_TIMEINFO_STMT, "get_stations_timeinfo_stmt", 4);
	get_throughstation_timeinfo_stmt = create_stmt(
			get_throughstation_timeinfo_stmt, GET_THROUGHSTATION_TIMEINFO_STMT,
			"get_throughstation_timeinfo_stmt", 1);
}

//关闭火车相关的预处理语句
void close_traininfo_stmt() {
	close_stmt(get_stationinfo_stmt, "traininfo_dao", "get_stationinfo_stmt");
	close_stmt(get_traininfo_stmt, "traininfo_dao", "get_traininfo_stmt");
	close_stmt(get_train_through_stationsinfo_stmt, "traininfo_dao",
			"get_train_through_stationsinfo_stmt");
	close_stmt(get_start_end_stationsinfo_stmt, "traininfo_dao",
			"get_start_end_stationsinfo_stmt");
	close_stmt(get_stations_timeinfo_stmt, "traininfo_dao",
			"get_stations_timeinfo_stmt");
	close_stmt(get_throughstation_timeinfo_stmt, "traininfo_dao",
			"get_throughstation_timeinfo_stmt");
}

//创建订单相关的预处理语句
void create_orderinfo_stmt() {
	get_orderinfo_by_buy_date_stmt = create_stmt(get_orderinfo_by_buy_date_stmt,
			GET_ORDERINFO_BY_BUY_DATE_STMT, "get_orderinfo_by_buy_date_stmt",
			3);
	get_orderinfo_by_buy_date_with_orderID_stmt = create_stmt(
			get_orderinfo_by_buy_date_with_orderID_stmt,
			GET_ORDERINFO_BY_BUY_DATE_STMT_WITH_ORDERID,
			"get_orderinfo_by_buy_date_with_orderID_stmt", 4);
	get_orderinfo_by_buy_date_with_trainID_stmt = create_stmt(
			get_orderinfo_by_buy_date_with_trainID_stmt,
			GET_ORDERINFO_BY_BUY_DATE_STMT_WITH_TRAINID,
			"get_orderinfo_by_buy_date_with_trainID_stmt", 4);
	get_orderinfo_by_buy_date_with_all_stmt = create_stmt(
			get_orderinfo_by_buy_date_with_all_stmt,
			GET_ORDERINFO_BY_BUY_DATE_STMT_WITH_ALL,
			"get_orderinfo_by_buy_date_with_all_stmt", 5);
	get_orderinfo_by_depart_date_stmt = create_stmt(
			get_orderinfo_by_depart_date_stmt,
			GET_ORDERINFO_BY_DEPART_DATE_STMT,
			"get_orderinfo_by_depart_date_stmt", 3);
	get_orderinfo_by_depart_date_with_orderID_stmt = create_stmt(
			get_orderinfo_by_depart_date_with_orderID_stmt,
			GET_ORDERINFO_BY_DEPART_DATE_STMT_WITH_ORDERID,
			"get_orderinfo_by_depart_date_with_orderID_stmt", 4);
	get_orderinfo_by_depart_date_with_trainID_stmt = create_stmt(
			get_orderinfo_by_depart_date_with_trainID_stmt,
			GET_ORDERINFO_BY_DEPART_DATE_STMT_WITH_TRAINID,
			"get_orderinfo_by_depart_date_with_trainID_stmt", 4);
	get_orderinfo_by_depart_date_with_all_stmt = create_stmt(
			get_orderinfo_by_depart_date_with_all_stmt,
			GET_ORDERINFO_BY_DEPART_DATE_STMT_WITH_ALL,
			"get_orderinfo_by_depart_date_with_all_stmt", 5);
	delete_orderinfo_stmt = create_stmt(delete_orderinfo_stmt,
			DELETE_ORDERINFO_STMT, "delete_orderinfo_stmt", 2);
}

//关闭订单相关的预处理语句
void close_orderinfo_stmt() {
	close_stmt(get_orderinfo_by_buy_date_stmt, "orderinfo_dao",
			"get_orderinfo_by_buy_date_stmt");
	close_stmt(get_orderinfo_by_buy_date_with_orderID_stmt, "orderinfo_dao",
			"get_orderinfo_by_buy_date_with_orderID_stmt");
	close_stmt(get_orderinfo_by_buy_date_with_trainID_stmt, "orderinfo_dao",
			"get_orderinfo_by_buy_date_with_trainID_stmt");
	close_stmt(get_orderinfo_by_buy_date_with_all_stmt, "orderinfo_dao",
			"get_orderinfo_by_buy_date_with_all_stmt");
	close_stmt(get_orderinfo_by_depart_date_stmt, "orderinfo_dao",
			"get_orderinfo_by_depart_date_stmt");
	close_stmt(get_orderinfo_by_depart_date_with_orderID_stmt, "orderinfo_dao",
			"get_orderinfo_by_depart_date_with_orderID_stmt");
	close_stmt(get_orderinfo_by_depart_date_with_trainID_stmt, "orderinfo_dao",
			"get_orderinfo_by_depart_date_with_trainID_stmt");
	close_stmt(get_orderinfo_by_depart_date_with_all_stmt, "orderinfo_dao",
			"get_orderinfo_by_depart_date_with_all_stmt");
	close_stmt(delete_orderinfo_stmt, "orderinfo_dao", "delete_orderinfo_stmt");
}

//创建车票相关的预处理语句
void create_ticketinfo_stmt() {
	get_traininfo_for_ticketleft_stmt = create_stmt(
			get_traininfo_for_ticketleft_stmt,
			GET_TRAININFO_FOR_TICKETLEFT_STMT,
			"get_traininfo_for_ticketleft_stmt", 3);
	get_traininfo_for_ticketleft_with_trainID_stmt = create_stmt(
			get_traininfo_for_ticketleft_with_trainID_stmt,
			GET_TRAININFO_FOR_TICKETLEFT_WITH_TRAINID_STMT,
			"get_traininfo_for_ticketleft_with_trainID_stmt", 4);
	get_train_seatcount_stmt = create_stmt(get_train_seatcount_stmt,
			GET_TRAIN_SEATCOUNT_STMT, "get_train_seatcount_stmt", 1);
	get_train_sale_ticketcount_stmt = create_stmt(
			get_train_sale_ticketcount_stmt, GET_TRAIN_SALE_TICKETCOUNT_STMT,
			"get_train_sale_ticketcount_stmt", 2);
	insert_orderinfo_stmt = create_stmt(insert_orderinfo_stmt,
			INSERT_ORDERINFO_STMT, "insert_orderinfo_stmt", 9);
}

//关闭车票相关的预处理语句
void close_ticketinfo_stmt() {
	close_stmt(get_traininfo_for_ticketleft_stmt, "ticketinfo_dao",
			"get_traininfo_for_ticketleft_stmt");
	close_stmt(get_traininfo_for_ticketleft_with_trainID_stmt, "ticketinfo_dao",
			"get_traininfo_for_ticketleft_with_trainID_stmt");
	close_stmt(get_train_seatcount_stmt, "ticketinfo_dao",
			"get_train_seatcount_stmt");
	close_stmt(get_train_sale_ticketcount_stmt, "ticketinfo_dao",
			"get_train_sale_ticketcount_stmt");
	close_stmt(insert_orderinfo_stmt, "ticketinfo_dao",
			"insert_orderinfo_stmt");
}

//连接数据库，在进行一切操作之间先调用该方法以确保数据库连接正确
bool connect_db() {
	bool result = true;
	mysql_init(&connection);
	if (!mysql_real_connect(&connection, HOST, USER, PASSWD, DB, PORT, NULL,
			0)) {
		fprintf(stderr, "userinfo_dao connection failed\n");
		if (mysql_errno(&connection)) {
			fprintf(stderr, "Connection error %d: %s\n",
					mysql_errno(&connection), mysql_error(&connection));
		}
		result = false;
	}

//设置字符集
	if (mysql_set_character_set(&connection, "utf8")) {
		fprintf(stderr, "userinfo_dao set character set error %d: %s\n",
				mysql_errno(&connection), mysql_error(&connection));
	}

	create_userinfo_stmt();
	create_traininfo_stmt();
	create_orderinfo_stmt();
	create_ticketinfo_stmt();
	return result;
}

//断开数据库连接，释放资源
void close_connection() {
	close_userinfo_stmt();
	close_traininfo_stmt();
	close_orderinfo_stmt();
	close_ticketinfo_stmt();
	mysql_close(&connection);
}
